package com.code.constant;

public class SqlConstant {

    public static String lineChangerLinux = "\n";
    public static String lineChanger = lineChangerLinux;
    public static String mysqlUseDb = "use ai_gateway;" + lineChanger;
    public static String tableName = "tableName";
    public static String indexTableName = "indexTableName";

    public static String[] strangeNames = {"change", "interval"};

    public static final String INDEX_NAME = "u_i_${" + indexTableName + "}";
    public static final String drop_index_oracle_prefix = "DROP INDEX u_i_";
    public static final String TABLE_SCHEMA = "ai_gateway";

    public static String indexColumns = "indexColumns";
    public static String tableComment = "tableComment";
    public static String columnDefault = "defaultValue";
    public static String columnName = "columnName";
    public static String dataType = "dataType";
    public static String dataLength = "dataLength";
    public static String nullState = "nullState";
    public static String columnComment = "columnComment";

    public static String oracleTriggerName = "oracleTriggerName";
    public static String oracleColumnTriggerName = "oracleColumnTriggerName";
    public static String oracleSequenceName = "oracleSequenceName";

    public static String nullState_not_null = "not null";
    public static String nullState_default_null = "";

    public static String history_table_index_tradeDate = "trade_date";
    public static String history_table_name_suffix = "_his";

    public static final String newLine = "" + lineChanger;
    public static final String id_column_name = "id";

    public static String column_length = "(${" + dataLength + "})";
    public static String tableCreate_comment = "-- ${" + tableName + "}" + lineChanger;
    public static String tableCreate_start_mysql = tableCreate_comment
            + "create table if not exists ${" + tableName + "} (" + lineChanger;
    public static String tableDeclare_start_oracle = tableCreate_comment + "DECLARE" + lineChanger
            + "  isExistTable number(10);" + lineChanger
            + "  isExistSeq number(10);" + lineChanger
            + "BEGIN" + lineChanger
            + "  select count(1) into isExistTable from user_tables where table_name = upper('${" + tableName + "}');" + lineChanger
            + "  if isExistTable <= 0 then" + lineChanger
            + "    EXECUTE immediate ";
    public static String tableCreate_start_oracle = "'create table ${" + tableName + "} (" + lineChanger;

    public static String tableCreate_id_mysql = "    id  bigint(20)  not null  auto_increment," + lineChanger;
    public static String tableCreate_id_oracle = "    id  number(20)  not null," + lineChanger;

    public static String tableCreate_column_mysql_begin = "    ${" + columnName + "}  ${" + dataType + "}";
    public static String tableCreate_column_mysql_default = "   default ${" + columnDefault + "}  ";
    public static String tableCreate_column_mysql_end = "  ${" + nullState + "} comment '${" + columnComment + "}' ," + lineChanger;

    public static String tableCreate_column_mysql_binary_end = " BINARY  ${" + nullState + "}  comment '${" + columnComment + "}' ," + lineChanger;


    public static String tableCreate_column_oracle_begin = "    ${" + columnName + "}  ${" + dataType + "}";
    public static String tableCreate_column_oracle_default = "   default ${" + columnDefault + "}  ";
    public static String tableCreate_column_oracle_end = "  ${" + nullState + "} ," + lineChanger;

    public static String tableCreate_key_mysql = "    primary key (id)" + lineChanger;
    public static String tableCreate_key_oracle = "    primary key (id)" + lineChanger;

    public static String tableCreate_unique_mysql = "    ,unique index " + INDEX_NAME + "(${" + indexColumns + "})" + lineChanger;
    public static String tableCreate_index_mysql = "    ,index " + INDEX_NAME + "(${" + indexColumns + "})" + lineChanger;

    public static String tableCreate_end_mysql = ") COMMENT = '${" + tableComment + "}';" + lineChanger;
    public static String tableCreate_end_oracle = "    )';" + lineChanger;
    public static String tableDeclare_init_unique_oracle = "    EXECUTE immediate 'create unique index " + INDEX_NAME + " "
            + "on ${" + tableName + "}(${" + indexColumns + "})';" + lineChanger;
    public static String tableDeclare_init_index_oracle = "    EXECUTE immediate 'create index " + INDEX_NAME + " "
            + "on ${" + tableName + "}(${" + indexColumns + "})';" + lineChanger;
    public static String tableDeclare_end_oracle = "    select count(1) into isExistSeq from user_sequences where "
            + "sequence_name = upper('${" + oracleSequenceName + "}');" + lineChanger
            + "    if isExistSeq <= 0 then" + lineChanger
            + "        EXECUTE immediate 'create sequence ${" + oracleSequenceName + "} start with 1 increment by 1';" + lineChanger
            + "    end if;" + lineChanger
            + "    EXECUTE immediate 'create or replace trigger ${" + oracleTriggerName + "}" + lineChanger
            + "                  before insert on ${" + tableName + "}" + lineChanger
            + "                  for each row" + lineChanger
            + "               declare" + lineChanger
            + "                  nextid number;" + lineChanger
            + "               begin" + lineChanger
            + "                IF :new.id IS NULL or :new.id=0 THEN" + lineChanger
            + "                  select ${" + oracleSequenceName + "}.nextval" + lineChanger
            + "                  into nextid" + lineChanger
            + "                  from sys.dual;" + lineChanger
            + "                  :new.id:=nextid;" + lineChanger
            + "                end if;" + lineChanger
            + "               end ${" + oracleTriggerName + "};';" + lineChanger
            + "    end if;" + lineChanger
            + "end;" + lineChanger
            + lineChanger
            + "/" + lineChanger;

    public static String tableCreate_table_trigger_oracle = "create or replace trigger ${" + oracleColumnTriggerName + "} " + lineChanger
            + " before update on ${" + tableName + "} for each row " + lineChanger
            + " begin " + lineChanger
            + "   select sysdate into :new.${" + columnName + "} from dual;" + lineChanger
            + "end;" + lineChanger
            + "/" + lineChanger
             + lineChanger;


    public static String tableCreate_table_comment_oracle = "comment on table ${" + tableName + "} is '${" + tableComment + "}';" + lineChanger;
    public static String tableCreate_column_comment_oracle = "comment on column ${" + tableName + "}.${" + columnName + "} "
            + "is '${" + columnComment + "}';" + lineChanger;

    public static String tableCreate_history_tradeDate_mysql = "    trade_date  int(11)   comment '交易日期'," + lineChanger;
    public static String tableCreate_history_tradeDate_oracle = "    trade_date  number(11)   ," + lineChanger;

    public static String add_column_proc_mysql_begin = lineChanger + "drop procedure if exists pro_addColumn;" + lineChanger
            + "DELIMITER //" + lineChanger
            + "create procedure pro_addColumn() begin" + lineChanger
            + "if not exists(select 1 from information_schema.columns where table_schema = '" + TABLE_SCHEMA + "' "
            + "and table_name = '${" + tableName + "}' "
            + "and column_name = '${" + columnName + "}') then" + lineChanger
            + "alter table ${" + tableName + "} add column ${" + columnName + "} ${" + dataType + "}";
    public static String add_column_proc_mysql_modify = " ${" + nullState + "} comment '${" + columnComment + "}';" + lineChanger
            + "else alter table ${" + tableName + "} " + "modify column ${" + columnName + "} ${" + dataType + "}";
    public static String add_column_proc_mysql_modify_binary = " BINARY ${" + nullState + "}  comment '${" + columnComment + "}';" + lineChanger
            + "else alter table ${" + tableName + "} " + "modify column ${" + columnName + "} ${" + dataType + "}";
    public static String add_column_proc_mysql_end = " ${" + nullState + "} comment '${" + columnComment + "}';" + lineChanger
            + "end if;" + lineChanger
            + "end;" + lineChanger
            + "//" + lineChanger
            + "DELIMITER ;" + lineChanger
            + "call pro_addColumn;" + lineChanger
            + "drop procedure pro_addColumn;" + lineChanger;

    public static String add_column_proc_mysql_binary_end = " BINARY ${" + nullState + "}  comment '${" + columnComment + "}';" + lineChanger
            + "end if;" + lineChanger
            + "end;" + lineChanger
            + "//" + lineChanger
            + "DELIMITER ;" + lineChanger
            + "call pro_addColumn;" + lineChanger
            + "drop procedure pro_addColumn;" + lineChanger;

    public static String drop_index_mysql = "alter table ${" + tableName + "} drop index " + INDEX_NAME + ";" + lineChanger;
    public static String add_index_column_mysql_start = lineChanger + "drop procedure if exists pro_addIndex;" + lineChanger
            + "DELIMITER //" + lineChanger
            + "create procedure pro_addIndex() begin" + lineChanger
            + "if exists(SELECT 1 FROM mysql.innodb_index_stats WHERE"
            + " (table_name = upper('${" + tableName + "}') or table_name = lower('${" + tableName + "}')) "
            + "and index_name = '" + INDEX_NAME + "') then" + lineChanger
            + drop_index_mysql
            + "end if;" + lineChanger
            + "end;" + lineChanger
            + "//" + lineChanger
            + "DELIMITER ;" + lineChanger
            + "call pro_addIndex;" + lineChanger
            + "drop procedure pro_addIndex;" + lineChanger
            + "" + lineChanger;

    public static String add_unique_column_mysql_end = "create unique index " + INDEX_NAME
            + " on ${" + tableName + "} (${" + indexColumns + "});" + lineChanger;
    public static String add_index_column_mysql_end = "create index " + INDEX_NAME
            + " on ${" + tableName + "} (${" + indexColumns + "});" + lineChanger;
    public static String add_unique_column_mysql = add_index_column_mysql_start + add_unique_column_mysql_end;
    public static String add_index_column_mysql = add_index_column_mysql_start + add_index_column_mysql_end;

    public static String add_column_proc_oracle_begin = lineChanger + "declare " + lineChanger
            + "  i integer;" + lineChanger
            + "begin" + lineChanger
            + "  select count(1) into i from user_tab_columns where table_name = upper('${" + tableName + "}') "
            + "and column_name = upper('${" + columnName + "}');" + lineChanger
            + "  if i = 0" + lineChanger
            + "  then" + lineChanger
            + "  execute immediate '"
            + "alter table ${" + tableName + "} add ${" + columnName + "} ${" + dataType + "}";

    public static String add_column_proc_oracle_modify = " ${" + nullState + "}';" + lineChanger + "  else execute immediate "
            + "'alter table ${" + tableName + "} modify ${" + columnName + "} ${" + dataType + "}";


    public static String add_column_proc_oracle_modify1 = " ${" + nullState + "}';" + lineChanger + "  else execute immediate "
        + " ' alter table ${" + tableName + "}  rename column ${" + columnName + "} to temp';";
    /*修改原字段名name为name_tmp*/
    //alter table tb rename column name to name_tmp;
    public static String add_column_proc_oracle_modify2 = lineChanger +
            "  execute immediate '"
            + "alter table ${" + tableName + "} add ${" + columnName + "} ${" + dataType + "}";

    public static String add_column_proc_oracle_modify3 = " ${" + nullState + "}';" + lineChanger +
            "  execute immediate '"
            + "update ${" + tableName + "} set ${" + columnName + "} = temp';";

    public static String add_column_proc_oracle_modify4 = lineChanger +
            "  execute immediate '"
            + "alter table ${" + tableName + "} drop column temp';";


    public static String add_column_proc_oracle_end = lineChanger
            + "  end if;" + lineChanger
            + "end; " + lineChanger
            + "/" + lineChanger
            + lineChanger;

    public static String drop_index_oracle = "DROP INDEX " + INDEX_NAME + "";
    public static String add_index_column_oracle_start = lineChanger + "declare " + lineChanger
            + "  i integer;" + lineChanger
            + "  stmt varchar2(1000);" + lineChanger
            + "begin" + lineChanger
            + "  select count(1) into i from user_ind_columns where index_name = upper('" + INDEX_NAME + "');" + lineChanger
            + "  if i > 0" + lineChanger
            + "  then" + lineChanger
            + "  stmt := '" + drop_index_oracle + "';" + lineChanger
            + "  execute immediate stmt;" + lineChanger
            + "  end if;" + lineChanger
            + "end; " + lineChanger + lineChanger
            + "/" + lineChanger;
    public static String add_unique_column_oracle_end = "create unique index " + INDEX_NAME
            + " on ${" + tableName + "}(${" + indexColumns + "});" + lineChanger;
    public static String add_index_column_oracle_end = "create index " + INDEX_NAME
            + " on ${" + tableName + "}(${" + indexColumns + "});" + lineChanger;
    public static String add_unique_column_oracle = add_index_column_oracle_start + add_unique_column_oracle_end;
    public static String add_index_column_oracle = add_index_column_oracle_start + add_index_column_oracle_end;

}
